IgnoreNulls Property Example

This example sets the IgnoreNulls property of a new Index to True or False based on user input, and then demonstrates the effect on a Recordset with a record whose key field contains a Null value.

Sub IgnoreNullsX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim idxNew As Index
    Dim rstEmployees As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set tdfEmployees = dbsNorthwind!Employees

    With tdfEmployees
        ' Create a new Index object.
        Set idxNew = .CreateIndex("NewIndex")
        idxNew.Fields.Append idxNew.CreateField("Country")

        ' Set the IgnoreNulls property of the new Index object 
        ' based on the user's input.
        Select Case MsgBox("Set IgnoreNulls to True?", _
            vbYesNoCancel)
            Case vbYes
                idxNew.IgnoreNulls = True
            Case vbNo
                idxNew.IgnoreNulls = False
            Case Else
                dbsNorthwind.Close
                End
        End Select

        ' Append the new Index object to the Indexes 
        ' collection of the Employees table.
        .Indexes.Append idxNew
        .Indexes.Refresh
    End With

    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees")

    With rstEmployees
        ' Add a new record to the Employees table.
        .AddNew
        !FirstName = "Gary"
        !LastName = "Haarsager"
        .Update

        ' Use the new index to set the order of the records.
        .Index = idxNew.Name
        .MoveFirst

        Debug.Print "Index = " & .Index & _
            ", IgnoreNulls = " & idxNew.IgnoreNulls
        Debug.Print "  Country - Name"

        ' Enumerate the Recordset. The value of the 
        ' IgnoreNulls property will determine if the newly 
        ' added record appears in the output.
        Do While Not .EOF
            Debug.Print "    " & _
                IIf(IsNull(!Country), "[Null]", !Country) & _
                " - " & !FirstName & " " & !LastName
            .MoveNext
        Loop

        ' Delete new record because this is a demonstration.
        .Index = ""
        .Bookmark = .LastModified
        .Delete
        .Close
    End With

    ' Delete new Index because this is a demonstration.
    tdfEmployees.Indexes.Delete idxNew.Name
    dbsNorthwind.Close

End Sub